﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DBUtility
{

    public abstract class DbHelper
    {
        public static SqlConnection Conn = new SqlConnection(PubConstant.ConnectionString);
        public DbHelper()
        {
        }

        /// <summary>
        /// 执行存储过程(增,删,改) 固定的返回值参数 @Return
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static bool RunProcedure(string storedProcName, SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand(storedProcName, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                        parameter.Value = DBNull.Value;
                    cmd.Parameters.Add(parameter);
                }
            }
            if (Conn.State == ConnectionState.Closed) Conn.Open();
            cmd.ExecuteNonQuery();
            Conn.Close();
            if (cmd.Parameters["@Return"].Value.ToString() == "1")
                return true;
            else return false;
        }
        /// <summary>
        /// 执行存储过程(增,删,改) 固定的返回值参数 @Return
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static string RunProcedureString(string storedProcName, SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand(storedProcName, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                        parameter.Value = DBNull.Value;
                    cmd.Parameters.Add(parameter);
                }
            }
            if (Conn.State == ConnectionState.Closed) Conn.Open();
            cmd.ExecuteNonQuery();
            Conn.Close();
            return cmd.Parameters["@return"].Value.ToString();
        }
        /// <summary>
        /// 执行存储过程(查,不包含参数)
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <returns></returns>
        public static DataSet RunProcedure(string storedProcName)
        {
            SqlCommand cmd = new SqlCommand(storedProcName, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            Conn.Close();
            return ds;
        }

        /// <summary>
        /// 执行存储过程(查,包含参数)
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static DataSet RunProcedureS(string storedProcName, SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand(storedProcName, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                        parameter.Value = DBNull.Value;
                    cmd.Parameters.Add(parameter);
                }
            }
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            Conn.Close();
            return ds;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static DataSet Query(string strSql)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(strSql, Conn);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                Conn.Close();
            }
            return ds;
        }
        public static string Encrypt(string sTr)
        {
            return Arithmetic.Encrypt(sTr);
        }
        public static string Decrypt(string sTr)
        {
            return Arithmetic.Decrypt(sTr);
        }
    }

}
